package com.atguigu.dga.util;

import com.atguigu.dga.governance.assessor.calc.SqlSimpleProcessAssessor;
import org.apache.hadoop.hive.ql.lib.DefaultGraphWalker;
import org.apache.hadoop.hive.ql.lib.Dispatcher;
import org.apache.hadoop.hive.ql.parse.*;

import java.util.Collections;

/**
 * ClassName: SqlUtil
 * Package: com.atguigu.springbootdemo.util
 * Description:
 *
 * @Author:
 * @Create: 2024/3/13 19:07
 * @Version: 1.0
 */
public class SqlUtil {
    // TODO 将给定sql 解析成AstNode 并遍历，按照传入的处理器对每个节点处理
    public static void parseSql(String sql, Dispatcher dispatcher) throws ParseException, SemanticException {
        // 1 创建 ParseDriver
        ParseDriver parseDriver = new ParseDriver();
        // 2 解析成astNode
        ASTNode astNode = parseDriver.parse(sql);
        while(astNode.getType() != HiveParser.TOK_QUERY) { // 根节点nil切换为TOK_QUERY
            // 将当前节点的第一个孩子 作为根节点
            ASTNode child = (ASTNode)astNode.getChild(0);
            astNode = child;
        }

        // 3 遍历树
        DefaultGraphWalker defaultGraphWalker = new DefaultGraphWalker(dispatcher);
        defaultGraphWalker.startWalking(Collections.singleton(astNode), null);


    }

    // 测试
    public static void main(String[] args) throws ParseException, SemanticException {
//        String sql = "select a,b,c from t where a = 123";
        String sql = "with\n" +
                "sku as\n" +
                "(\n" +
                "    select\n" +
                "        id,\n" +
                "        price,\n" +
                "        sku_name,\n" +
                "        sku_desc,\n" +
                "        weight,\n" +
                "        is_sale,\n" +
                "        spu_id,\n" +
                "        category3_id,\n" +
                "        tm_id,\n" +
                "        create_time\n" +
                "    from gmall.ods_sku_info_full\n" +
                "    where dt='${do_date}'\n" +
                "),\n" +
                "spu as\n" +
                "(\n" +
                "    select\n" +
                "        id,\n" +
                "        spu_name\n" +
                "    from gmall.ods_spu_info_full\n" +
                "    where dt='${do_date}'\n" +
                "),\n" +
                "c3 as\n" +
                "(\n" +
                "    select\n" +
                "        id,\n" +
                "        name,\n" +
                "        category2_id\n" +
                "    from gmall.ods_base_category3_full\n" +
                "    where dt='${do_date}'\n" +
                "),\n" +
                "c2 as\n" +
                "(\n" +
                "    select\n" +
                "        id,\n" +
                "        name,\n" +
                "        category1_id\n" +
                "    from gmall.ods_base_category2_full\n" +
                "    where dt='${do_date}'\n" +
                "),\n" +
                "c1 as\n" +
                "(\n" +
                "    select\n" +
                "        id,\n" +
                "        name\n" +
                "    from gmall.ods_base_category1_full\n" +
                "    where dt='${do_date}'\n" +
                "),\n" +
                "tm as\n" +
                "(\n" +
                "    select\n" +
                "        id,\n" +
                "        tm_name\n" +
                "    from gmall.ods_base_trademark_full\n" +
                "    where dt='${do_date}'\n" +
                "),\n" +
                "attr as\n" +
                "(\n" +
                "    select\n" +
                "        sku_id,\n" +
                "        collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs\n" +
                "    from gmall.ods_sku_attr_value_full\n" +
                "    where dt='${do_date}'\n" +
                "    group by sku_id\n" +
                "),\n" +
                "sale_attr as\n" +
                "(\n" +
                "    select\n" +
                "        sku_id,\n" +
                "        collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs\n" +
                "    from gmall.ods_sku_sale_attr_value_full\n" +
                "    where dt='${do_date}'\n" +
                "    group by sku_id\n" +
                ")\n" +
                "insert overwrite table gmall.dim_sku_full partition(dt='${do_date}')\n" +
                "select\n" +
                "    sku.id,\n" +
                "    sku.price,\n" +
                "    sku.sku_name,\n" +
                "    sku.sku_desc,\n" +
                "    sku.weight,\n" +
                "    sku.is_sale,\n" +
                "    sku.spu_id,\n" +
                "    spu.spu_name,\n" +
                "    sku.category3_id,\n" +
                "    c3.name,\n" +
                "    c3.category2_id,\n" +
                "    c2.name,\n" +
                "    c2.category1_id,\n" +
                "    c1.name,\n" +
                "    sku.tm_id,\n" +
                "    tm.tm_name,\n" +
                "    attr.attrs,\n" +
                "    sale_attr.sale_attrs,\n" +
                "    sku.create_time\n" +
                "from sku\n" +
                "left join spu on sku.spu_id=spu.id\n" +
                "left join c3 on sku.category3_id=c3.id\n" +
                "left join c2 on c3.category2_id=c2.id\n" +
                "left join c1 on c2.category1_id=c1.id\n" +
                "left join tm on sku.tm_id=tm.id\n" +
                "left join attr on sku.id=attr.sku_id\n" +
                "left join sale_attr on sku.id=sale_attr.sku_id";

        SqlSimpleProcessAssessor.SimpleProcessDispatcher simpleProcessDispatcher = new SqlSimpleProcessAssessor.SimpleProcessDispatcher("gmall");
        SqlUtil.parseSql(sql, simpleProcessDispatcher);
    }
    public static String filterUnsafeSql(String input) {
        if (input == null) {
            return null;
        }

        // 替换 MySQL 中可能导致 SQL 注入的特殊字符
        return input.replace("\\", "\\\\")
                .replace("'", "\\'")
                .replace("\"", "\\\"")
                .replace("\b", "\\b")
                .replace("\n", "\\n")
                .replace("\r", "\\r")
                .replace("\t", "\\t")
                .replace("\u001A", "\\Z")
                .replace("%", "\\%")
                .replace("_", "\\_");
    }

}
